IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].GetStandardReport') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].GetStandardReport
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO                      
/******************************************************************          
* Name:   GetStandardReport          
* Purpose:  Retrieves admpersonIDs and COIIds matching the report type  
  Note:This stored procedure not getting used as the standard report page is removed in COI application   
*          
* PARAMETERS          
* Name          Description               
* -------------      -------------------------------------------          
* @admPersonId      admPerosnId  (REQUIRED)            
* @reportType		standard report type to be generated.    
* @sortColumn		Column to sort on       
* @sortOrder		Determines whether or not the sort should be performed in ascending or descending order.
* @HierarchyTypeId. Users Hierarchy Type.  
*********************************************************************/          
CREATE PROCEDURE [dbo].[GetStandardReport]              
(    
@admPersonId int,    
@reportType varchar(100),    
@sortColumn varchar(150)= 'Name',    
@sortOrder bit=0, -->Asc,1-->Desc         
@HierarchyTypeId INT = 0  
)              
AS BEGIN              
              
SET NOCOUNT ON              
              
If @sortColumn='Name' or @sortColumn ='Supervisor' or @sortColumn='Title'    
  Begin   
 IF  @reportType = 'COISTDRPT_BOARDTRUSTEE'  
 BEGIN  
 PRINT @reportType  
  SELECT adp.[Id] as [PersonId],    
      coi.[Id]as COIId     
      from dbo.GetDescendants(@admpersonId,1,0,@HierarchyTypeId) sou    
  Inner Join AdmPerson adp    
  On sou.[ChildId]=adp.[Id]    
  Left outer Join ConflictOfInterest coi    
  On  coi.[admPersonId]=adp.[Id]    
  AND coi.[Id]= (Select Max(Id)as LatestCOIId from conflictofinterest where admPersonId=adp.[Id])    
  AND coi.[Status]in ('COISTATUS_SUBMITTED','COISTATUS_REVIEWED')    
  Left Outer Join AdmPerson adp1             
  on adp1.[Id]= sou.[ParentId]    
  Where  adp.[OutsideMember]= 1  
  Order by    
    Case @sortColumn    
  When 'Name' then 'adp.LastName'    
  When 'Supervisor' then 'adp1.LastName'    
  When 'Title' then 'Title'    
  Else 'adp.LastName'    
  END + ' ' +    
    CASE @sortOrder    
  When 1 then 'Desc'    
  When 0 then 'ASC'    
  Else 'ASC'    
  END    
 END  
 ELSE  
  BEGIN  
  SELECT adp.[Id] as [PersonId],    
  coi.[Id]as COIId     
  from dbo.GetDescendants(@admpersonId,1,0,@HierarchyTypeId) sou    
  Inner Join AdmPerson adp    
  On sou.[ChildId]=adp.[Id]    
  Left outer Join ConflictOfInterest coi    
  On  coi.[admPersonId]=adp.[Id]    
  AND coi.[Id]= (Select Max(Id)as LatestCOIId from conflictofinterest where admPersonId=adp.[Id])    
  AND coi.[Status]in ('COISTATUS_SUBMITTED','COISTATUS_REVIEWED')    
  Left Outer Join AdmPerson adp1             
   on adp1.[Id]= sou.[ParentId]    
  Where     
  isNULL(adp.[COIRole],'*') = Case @reportType   
     When 'COISTDRPT_PRESIDENT' then 'COIROLE_PRESIDENT'  
     When 'COISTDRPT_UNITDIVISIONCHIEF' then 'COIROLE_UNITDIVISIONCHIEF'  
     When 'COISTDRPT_CHIEFCHAIRVICEPRESIDENT' then 'COIROLE_CHIEFCHAIR'  
     ELSE 'COISTDRPT_PRESIDENT'  
     END  
     Order by    
     Case @sortColumn    
      When 'Name' then 'adp.LastName'    
      When 'Supervisor' then 'adp1.LastName'    
      When 'Title' then 'Title'    
      Else 'adp.LastName'    
      END + ' ' +    
     CASE @sortOrder    
      When 1 then 'Desc'    
      When 0 then 'ASC'    
      Else 'ASC'    
      END    
  END    
 END  
 Else If @sortColumn='PolicyViolation' or @sortColumn='OutsideActivity' or @sortColumn='EquityInterest' or @sortColumn='FamilyInterest' or @sortColumn='OtherActivity'    
  BEGIN   
 IF  @reportType = 'COISTDRPT_BOARDTRUSTEE'  
 BEGIN  
  SELECT adp.[Id] as [PersonId],    
     coi.[Id]as COIId     
    from dbo.GetDescendants(@admpersonId,1,0,@HierarchyTypeId) sou    
  Inner Join AdmPerson adp    
   On sou.[ChildId]=adp.[Id]    
  Left outer Join ConflictOfInterest coi    
   On  coi.[admPersonId]=adp.[Id]    
   AND coi.[Id]= (Select Max(Id)as LatestCOIId from conflictofinterest where admPersonId=adp.[Id])    
   AND coi.[Status]in ('COISTATUS_SUBMITTED','COISTATUS_REVIEWED')    
  Where     
        adp.[OutsideMember]= 1      
   Order by    
   Case @sortColumn    
    When 'PolicyViolation' then 'PolicyViolation'    
    When 'OutsideActivity' then 'HasOutsideActivities'    
    When 'EquityInterest' then 'HasEquityInterests'    
    When 'FamilyInterest' then 'HasFamilyAssociations'    
    When 'OtherActivity' then 'OtherActivity'    
    Else'PolicyViolation'    
    END + ' ' +    
   CASE @sortOrder    
    When 1 then 'Desc'    
    When 0 then 'ASC'    
    Else 'ASC'    
    END    
 END  
ELSE   
 BEGIN  
    SELECT adp.[Id] as [PersonId],    
      coi.[Id]as COIId     
     from dbo.GetDescendants(@admpersonId,1,0,@HierarchyTypeId) sou    
		  Inner Join AdmPerson adp    
		   On sou.[ChildId]=adp.[Id]    
		  Left outer Join ConflictOfInterest coi    
		   On  coi.[admPersonId]=adp.[Id]    
		   AND coi.[Id]= (Select Max(Id)as LatestCOIId from conflictofinterest where admPersonId=adp.[Id])    
		   AND coi.[Status]in ('COISTATUS_SUBMITTED','COISTATUS_REVIEWED')    
		  Left Outer Join AdmPerson adp1             
		   on adp1.[Id]= sou.[ParentId]    
		  Where     
			 (isNULL(adp.[COIRole],'*') = Case @reportType   
			 When 'COISTDRPT_PRESIDENT' then 'COIROLE_PRESIDENT'  
			 When 'COISTDRPT_UNITDIVISIONCHIEF' then 'COIROLE_UNITDIVISIONCHIEF'  
			 When 'COISTDRPT_CHIEFCHAIRVICEPRESIDENT' then 'COIROLE_CHIEFCHAIR'  
			 ELSE 'COISTDRPT_PRESIDENT'  
			 END)         
		   Order by    
		   Case @sortColumn    
			When 'PolicyViolation' then 'PolicyViolation'    
			When 'OutsideActivity' then 'HasOutsideActivities'    
			When 'EquityInterest' then 'HasEquityInterests'    
			When 'FamilyInterest' then 'HasFamilyAssociations'    
			When 'OtherActivity' then 'OtherActivity'    
			Else'PolicyViolation'    
			END + ' ' +    
		   CASE @sortOrder    
			When 1 then 'Desc'    
			When 0 then 'ASC'    
			Else 'ASC'    
			END    
  END    
END   
 Else    
  BEGIN   
 IF  @reportType = 'COISTDRPT_BOARDTRUSTEE'  
 BEGIN  
  SELECT adp.[Id] as [PersonId],    
       coi.[Id]as COIId        
  from dbo.GetDescendants(@admpersonId,1,0,@HierarchyTypeId) sou    
  Inner Join AdmPerson adp    
   On sou.[ChildId]=adp.[Id]    
  Left outer Join ConflictOfInterest coi    
   On  coi.[admPersonId]=adp.[Id]    
   AND coi.[Id]= (Select Max(Id)as LatestCOIId from conflictofinterest where admPersonId=adp.[Id])    
   AND coi.[Status]in ('COISTATUS_SUBMITTED','COISTATUS_REVIEWED')    
  Left Outer Join AdmPerson adp1             
   on adp1.[Id]= sou.[ParentId]    
  Where     
       adp.[OutsideMember]= 1          
   Order by    
   Case @sortColumn    
    When 'DateSubmitted' then 'DateSubmitted'    
    Else'DateSubmitted'    
    END + ' ' +    
   CASE @sortOrder    
    When 1 then 'Desc'    
    When 0 then 'ASC'    
    Else 'ASC'    
    END    
 END  
 ELSE  
 BEGIN   
    SELECT adp.[Id] as [PersonId],    
       coi.[Id]as COIId     
     from dbo.GetDescendants(@admpersonId,1,0,@HierarchyTypeId) sou    
  Inner Join AdmPerson adp    
   On sou.[ChildId]=adp.[Id]    
  Left outer Join ConflictOfInterest coi    
   On  coi.[admPersonId]=adp.[Id]    
   AND coi.[Id]= (Select Max(Id)as LatestCOIId from conflictofinterest where admPersonId=adp.[Id])    
   AND coi.[Status]in ('COISTATUS_SUBMITTED','COISTATUS_REVIEWED')    
  Left Outer Join AdmPerson adp1             
   on adp1.[Id]= sou.[ParentId]    
  Where     
     (isNULL(adp.[COIRole],'*') = Case @reportType   
     When 'COISTDRPT_PRESIDENT' then 'COIROLE_PRESIDENT'  
     When 'COISTDRPT_UNITDIVISIONCHIEF' then 'COIROLE_UNITDIVISIONCHIEF'  
     When 'COISTDRPT_CHIEFCHAIRVICEPRESIDENT' then 'COIROLE_CHIEFCHAIR'  
     ELSE 'COISTDRPT_PRESIDENT'  
     END)         
   Order by    
   Case @sortColumn    
    When 'DateSubmitted' then 'DateSubmitted'    
    Else'DateSubmitted'    
    END + ' ' +    
   CASE @sortOrder    
    When 1 then 'Desc'    
    When 0 then 'ASC'    
    Else 'ASC'    
    END    
  END     
END            
  END  
  